package cn.wh.xwm.hibernate.dao;

import java.text.ParseException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.LockMode;
import org.hibernate.Query;
import org.hibernate.criterion.Example;

import cn.wh.xwm.common.util.Constant;
import cn.wh.xwm.common.util.TimeUtils;
import cn.wh.xwm.controller.action.vo.LiquidItem;
import cn.wh.xwm.controller.action.vo.report.custinout.Application;
import cn.wh.xwm.controller.action.vo.report.custinout.Categories;
import cn.wh.xwm.controller.action.vo.report.custinout.Category;
import cn.wh.xwm.controller.action.vo.report.custinout.CategoryA;
import cn.wh.xwm.controller.action.vo.report.custinout.Chart;
import cn.wh.xwm.controller.action.vo.report.custinout.CustomerInAndOutJson;
import cn.wh.xwm.controller.action.vo.report.custinout.Data;
import cn.wh.xwm.controller.action.vo.report.custinout.Dataset;
import cn.wh.xwm.controller.action.vo.report.custinout.Definition;
import cn.wh.xwm.controller.action.vo.report.custinout.Styles;

/**
 * A data access object (DAO) providing persistence and search support for
 * PmLiquid entities. Transaction control of the save(), update() and delete()
 * operations can directly support Spring container-managed transactions or they
 * can be augmented to handle user-managed Spring transactions. Each of these
 * methods provides additional information for how to configure it for the
 * desired type of transaction control.
 * 
 * @see cn.wh.xwm.hibernate.dao.PmLiquid
 * @author MyEclipse Persistence Tools
 */

public class PmLiquidDAO extends BaseHibernateDAO {
	private static final Log log = LogFactory.getLog(PmLiquidDAO.class);
	// property constants
	public static final String PRODUCT_ID = "productId";
	public static final String CUSTOMER_ID_FROM = "customerIdFrom";
	public static final String IN_OR_OUT = "inOrOut";
	public static final String AMOUNT = "amount";
	public static final String PATCH_NUMBER = "patchNumber";
	public static final String CUSTOMER_ID_TO = "customerIdTo";
	public static final String UPLOADFILENAME = "uploadfilename";

	public void save(PmLiquid transientInstance) {
		log.debug("saving PmLiquid instance");
		try {
			getSession().save(transientInstance);
			log.debug("save successful");
		} catch (RuntimeException re) {
			log.error("save failed", re);
			throw re;
		}
	}

	public void delete(PmLiquid persistentInstance) {
		log.debug("deleting PmLiquid instance");
		try {
			getSession().delete(persistentInstance);
			log.debug("delete successful");
		} catch (RuntimeException re) {
			log.error("delete failed", re);
			throw re;
		}
	}

	public PmLiquid findById(java.lang.String id) {
		log.debug("getting PmLiquid instance with id: " + id);
		try {
			PmLiquid instance = (PmLiquid) getSession().get(
					"cn.wh.xwm.hibernate.dao.PmLiquid", id);
			return instance;
		} catch (RuntimeException re) {
			log.error("get failed", re);
			throw re;
		}
	}

	public List findByExample(PmLiquid instance) {
		log.debug("finding PmLiquid instance by example");
		try {
			List results = getSession().createCriteria(
					"cn.wh.xwm.hibernate.dao.PmLiquid").add(
					Example.create(instance)).list();
			log.debug("find by example successful, result size: "
					+ results.size());
			return results;
		} catch (RuntimeException re) {
			log.error("find by example failed", re);
			throw re;
		}
	}

	public List findByProperty(String propertyName, Object value) {
		log.debug("finding PmLiquid instance with property: " + propertyName
				+ ", value: " + value);
		try {
			String queryString = "from PmLiquid as model where model."
					+ propertyName + "= ?";
			Query queryObject = getSession().createQuery(queryString);
			queryObject.setParameter(0, value);
			return queryObject.list();
		} catch (RuntimeException re) {
			log.error("find by property name failed", re);
			throw re;
		}
	}

	public List findByProductId(Object productId) {
		return findByProperty(PRODUCT_ID, productId);
	}

	public List findByCustomerIdFrom(Object customerIdFrom) {
		return findByProperty(CUSTOMER_ID_FROM, customerIdFrom);
	}

	public List findByInOrOut(Object inOrOut) {
		return findByProperty(IN_OR_OUT, inOrOut);
	}

	public List findByAmount(Object amount) {
		return findByProperty(AMOUNT, amount);
	}

	public List findByPatchNumber(Object patchNumber) {
		return findByProperty(PATCH_NUMBER, patchNumber);
	}

	public List findByCustomerIdTo(Object customerIdTo) {
		return findByProperty(CUSTOMER_ID_TO, customerIdTo);
	}

	public List findByUploadfilename(Object uploadfilename) {
		return findByProperty(UPLOADFILENAME, uploadfilename);
	}

	public List findAll() {
		log.debug("finding all PmLiquid instances");
		try {
			String queryString = "from PmLiquid";
			Query queryObject = getSession().createQuery(queryString);
			return queryObject.list();
		} catch (RuntimeException re) {
			log.error("find all failed", re);
			throw re;
		}
	}

	public PmLiquid merge(PmLiquid detachedInstance) {
		log.debug("merging PmLiquid instance");
		try {
			PmLiquid result = (PmLiquid) getSession().merge(detachedInstance);
			log.debug("merge successful");
			return result;
		} catch (RuntimeException re) {
			log.error("merge failed", re);
			throw re;
		}
	}

	public void attachDirty(PmLiquid instance) {
		log.debug("attaching dirty PmLiquid instance");
		try {
			getSession().saveOrUpdate(instance);
			log.debug("attach successful");
		} catch (RuntimeException re) {
			log.error("attach failed", re);
			throw re;
		}
	}

	public void attachClean(PmLiquid instance) {
		log.debug("attaching clean PmLiquid instance");
		try {
			getSession().lock(instance, LockMode.NONE);
			log.debug("attach successful");
		} catch (RuntimeException re) {
			log.error("attach failed", re);
			throw re;
		}
	}

	/**
	 * 存PM流向到记录表
	 * 
	 * @param list
	 * @param uploadfileFileName
	 * @param productId
	 * @param customerid
	 */
	public void save(ArrayList<LiquidItem> list, String uploadfileFileName,
			String productId, String customerId) {
		InventoryinfoDAO idao = new InventoryinfoDAO();
		CustomerinfoDAO custdao = new CustomerinfoDAO();
		Customerinfo customerinfo;
		PmLiquid pmLiquid;
		for (LiquidItem item : list) {
			String custname = item.getCustomerName();
			List<Customerinfo> custlist = custdao.findByName(custname);
			if (custlist.size() == 0) {
				customerinfo = new Customerinfo();
				customerinfo.setName(custname);
				customerinfo.setValid(1);
				custdao.save(customerinfo);
			} else {
				customerinfo = custlist.get(0);
			}
			pmLiquid = new PmLiquid();
			pmLiquid.setAmount(Integer.valueOf(item.getAmount()));
			pmLiquid.setPatchNumber(item.getPatchNumber());
			pmLiquid.setProductId(productId);
			pmLiquid.setUploadfilename(uploadfileFileName);
			pmLiquid.setEventDate(item.getEventDate());
			pmLiquid.setInOrOut(Constant.INVENTORY_IN.equals(item
					.getInOrOut()) ? 0 : 1);
			if (pmLiquid.getInOrOut() == 0) {
				pmLiquid.setCustomerIdFrom(customerinfo.getId());
				pmLiquid.setCustomerIdTo(customerId);
				//增加记录时 入库增加库存
				idao.increaseInventory(customerId, productId, item.getAmount());
			} else {
				pmLiquid.setCustomerIdFrom(customerId);
				pmLiquid.setCustomerIdTo(customerinfo.getId());
				//增加记录时 出库删除库存
				idao.reduceInventory(customerId, productId, item.getAmount());
			}
			this.save(pmLiquid);
		}

	}

	/**
	 * 删除PM流向记录
	 * @param list
	 * @param filename
	 * @param productId
	 * @param customerid
	 */
	public void delete(ArrayList<LiquidItem> list, String filename,
			String productId, String customerid) {
		InventoryinfoDAO idao = new InventoryinfoDAO();
		CustomerinfoDAO custdao = new CustomerinfoDAO();
		Customerinfo customerinfo;
		PmLiquid pmLiquid;
		for (LiquidItem item : list) {
			String custname = item.getCustomerName();
			List<Customerinfo> custlist = custdao.findByName(custname);
			if (custlist.size() == 0) {
				customerinfo = new Customerinfo();
				customerinfo.setName(custname);
				customerinfo.setValid(1);
				custdao.save(customerinfo);
			} else {
				customerinfo = custlist.get(0);
			}
			pmLiquid = new PmLiquid();
			pmLiquid.setAmount(Integer.valueOf(item.getAmount()));
			pmLiquid.setPatchNumber(item.getPatchNumber());
			pmLiquid.setProductId(productId);
			pmLiquid.setUploadfilename(filename);
			pmLiquid.setEventDate(item.getEventDate());
			pmLiquid.setInOrOut(Constant.INVENTORY_IN.endsWith(item
					.getInOrOut()) ? 0 : 1);
			if (pmLiquid.getInOrOut() == 0) {
				pmLiquid.setCustomerIdFrom(customerinfo.getId());
				pmLiquid.setCustomerIdTo(customerid);
				//删除记录时 入库减少库存
				idao.reduceInventory(customerid, productId, item.getAmount());
			} else {
				pmLiquid.setCustomerIdFrom(customerid);
				pmLiquid.setCustomerIdTo(customerinfo.getId());
				//删除记录时 出库增加库存
				idao.increaseInventory(customerid, productId, item.getAmount());
			}
			List<PmLiquid> templist = this.findByExample(pmLiquid);
			for (PmLiquid li : templist) {
				this.delete(li);
			}
		}

	}

	public CustomerInAndOutJson findForReport(String customerNameId,
			String eventType, String startDate, String endDate) throws ParseException {
		CustomerInAndOutJson json;
		CustomerinfoDAO cdao = new CustomerinfoDAO();
		Customerinfo cinfo = cdao.findById(customerNameId);

		ArrayList<Integer> months = TimeUtils.getMonthesFromTwoDate(TimeUtils
				.fromIsoDate(startDate), TimeUtils.fromIsoDate(endDate));
		String event = eventType.equals("0") ? "采购入库" : "销售出库";

		String title = cinfo.getName();
		if (months.size() == 1) {
			title = months.get(0) + "月" + event + "报表";
		} else {
			title = months.get(0) + "月" + "---" + months.get(months.size() - 1)
					+ "月" + event + "报表";
		}
		json = this.prepare(title);

		// 设置X轴
		Categories categories = new Categories();
		json.setCategories(categories);
		ArrayList<Category> calist = new ArrayList<Category>();
		categories.setCategory(calist);

		for (Integer x : months) {
			CategoryA ca = new CategoryA();
			ca.setLabel(TimeUtils.parseNumberToMonthCHN(x));
			calist.add(ca);
		}

		ProductinfoDAO pdao = new ProductinfoDAO();
		List<Productinfo> plist = pdao.findAll();

		for (Productinfo p : plist) {
			Dataset dset = new Dataset();
			json.setDataset(dset);
			dset.setSeriesname(p.getName());
			for (Integer x : months) {
				Data d = new Data();
				String value = this.findAmountByDateAndCustomer(p.getId(),
						startDate, endDate, x, customerNameId, eventType);
				d.setValue(value);
				dset.setData(d);
			}
		}
		// 0采购入库 1销售出库

		return json;
	}

	private String findAmountByDateAndCustomer(String productId,
			String startDate, String endDate, Integer x, String customerNameId,
			String eventType) {
		String sql;
		if (eventType.equals("0")) {
			sql = "select sum(amount) from snfims.pm_liquid where product_id = '"
					+ productId
					+ "' and event_date > '"
					+ startDate
					+ "' and event_date < '"
					+ endDate
					+ "' and month(event_date)='"
					+ x
					+ "' and customer_id_to ='"
					+ customerNameId
					+ "' and in_or_out='0'";
		} else {
			sql = "select sum(amount) from snfims.pm_liquid where product_id = '"
					+ productId
					+ "' and event_date > '"
					+ startDate
					+ "' and event_date < '"
					+ endDate
					+ "' and month(event_date)='"
					+ x
					+ "' and customer_id_from ='"
					+ customerNameId
					+ "' and in_or_out='1'";
		}
		return this.findSum(sql);
	}

	public static void main(String[] args) {
		System.out.println("2003-20323".substring(0, 4));
	}

	private CustomerInAndOutJson prepare(String title) {
		CustomerInAndOutJson json = new CustomerInAndOutJson();
		Chart chart = new Chart();
		chart.setCaption(title);
		chart.setNumberprefix("支");
		chart.setShowvalues("1");
		chart.setXaxisname("月份");
		chart.setYaxisname("销量");
		json.setChart(chart);
		Styles styles = new Styles();
		Definition definition = new Definition();
		definition.setDuration("1");
		definition.setName("CanvasAnim");
		definition.setParam("_xScale");
		definition.setStart("0");
		definition.setType("animation");
		ArrayList<Definition> delist = new ArrayList<Definition>();
		delist.add(definition);
		styles.setDefinition(delist);
		ArrayList<Application> applist = new ArrayList<Application>();
		Application app = new Application();
		applist.add(app);
		app.setStyles("CanvasAnim");
		app.setToobject("Canvas");
		styles.setApplication(applist);
		json.setStyles(styles);
		return json;
	}

	/**
	 * 销售出入对比报表
	 * 
	 * @param salesId
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	public CustomerInAndOutJson findForReport(String salesId, String startDate,
			String endDate) {
		UserinfoDAO udao = new UserinfoDAO();
		Userinfo user = udao.findById(salesId);
		String title = user.getUsername() + "销售出入对比报表";
		CustomerInAndOutJson json = prepareForDifferent(title);

		Categories categories = new Categories();
		json.setCategories(categories);
		ArrayList<Category> calist = new ArrayList<Category>();
		categories.setCategory(calist);

		ProductinfoDAO pdao = new ProductinfoDAO();
		List<Productinfo> plist = pdao.findAll();
		for (Productinfo p : plist) {
			CategoryA ca = new CategoryA();
			ca.setLabel(p.getName());
			calist.add(ca);
		}
		// sales数据
		Dataset dset = new Dataset();
		json.setDataset(dset);
		dset.setSeriesname(user.getUsername());
		for (Productinfo p : plist) {
			Data d = new Data();
			String value = this.findbySales(salesId, p.getId(), startDate,
					endDate);
			d.setValue(value);
			d.setLink("j-myJS-" + salesId + "," + p.getId() + "," + startDate
					+ "," + endDate);
			dset.setData(d);
		}
		// pm数据
		dset = new Dataset();
		json.setDataset(dset);
		dset.setSeriesname("PM 数据");
		for (Productinfo p : plist) {
			Data d = new Data();
			String value = this
					.findbyPM(salesId, p.getId(), startDate, endDate);
			d.setLink("j-myJS-" + salesId + "," + p.getId() + "," + startDate
					+ "," + endDate);
			d.setValue(value);
			dset.setData(d);
		}

		return json;
	}

	private String findbyPM(String salesId, String productId, String startDate,
			String endDate) {
		Integer total = 0;
		// 根据salesId 获取相关的辖区
		UserCustomerRelationDAO userCustomerRelationDAO = new UserCustomerRelationDAO();
		List<UserCustomerRelation> list = userCustomerRelationDAO
				.findByProperty("id.userId", salesId);
		for (UserCustomerRelation info : list) {
			String customerId = info.getId().getCustomerId();
			String sql = "select sum(amount) from snfims.pm_liquid where product_id ='"
					+ productId
					+ "' and in_or_out=1 and customer_id_to='"
					+ customerId
					+ "' and event_date >'"
					+ startDate
					+ "' and event_date <'"
					+ endDate + "' ";
			String number = this.findSum(sql);
			total += Integer.valueOf(number);
		}
		return total.toString();
	}
	
	public  String findbyPM(String salesId, String productId, String customerId,String startDate,
			String endDate) {
		UserCustomerRelationDAO userCustomerRelationDAO = new UserCustomerRelationDAO();
		List<UserCustomerRelation> list = userCustomerRelationDAO
		.findByProperty("id.userId", salesId);
			String sql = "select sum(amount) from snfims.pm_liquid where product_id ='"
				+ productId
				+ "' and in_or_out=1 and customer_id_to='"
				+ customerId 
				+ "' and event_date >'"
				+ startDate
				+ "' and event_date <'"
				+ endDate + "' ";
		String total = this.findSum(sql);
		return total;
	}

	private String findbySales(String salesId, String productId,
			String startDate, String endDate) {
		String sql = "select sum(amount) from snfims.sales_liquid where user_id='"
				+ salesId
				+ "' and product_id='"
				+ productId
				+ "' and event_date >'"
				+ startDate
				+ "' and event_date <'"
				+ endDate + "' ";
		return this.findSum(sql);
	}
	
	public String findbySales(String salesId, String productId,String customerId,
			String startDate, String endDate) {
		String sql = "select sum(amount) from snfims.sales_liquid where user_id='"
			+ salesId
			+ "' and product_id='"
			+ productId
			+ "' and customer_id_to = '"
			+ customerId
			+ "' and event_date >'"
			+ startDate
			+ "' and event_date <'"
			+ endDate + "' ";
		return this.findSum(sql);
	}

	private CustomerInAndOutJson prepareForDifferent(String title) {
		CustomerInAndOutJson json = new CustomerInAndOutJson();
		Chart chart = new Chart();
		chart.setCaption(title);
		chart.setNumberprefix("支");
		chart.setShowvalues("1");
		chart.setXaxisname("产品");
		chart.setYaxisname("销量");
		json.setChart(chart);
		Styles styles = new Styles();
		Definition definition = new Definition();
		definition.setDuration("1");
		definition.setName("CanvasAnim");
		definition.setParam("_xScale");
		definition.setStart("0");
		definition.setType("animation");
		ArrayList<Definition> delist = new ArrayList<Definition>();
		delist.add(definition);
		styles.setDefinition(delist);
		ArrayList<Application> applist = new ArrayList<Application>();
		Application app = new Application();
		applist.add(app);
		app.setStyles("CanvasAnim");
		app.setToobject("Canvas");
		styles.setApplication(applist);
		json.setStyles(styles);
		return json;
	}
}